﻿using System;
using System.Collections;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using GeneralLibs;
namespace Lists
{
    public class HanSuDung : DoiTuong
    {
        public enum UpdateSoLuongTrongHanSuDung
        {
            KhongTangKhongGiam = 0,
            Tang = 1,
            Giam = 2
        }

        #region Guid VatTuHangHoaID property
        public Guid VatTuHangHoaID
        {
            get
            {
                if (_DataRow["VatTuHangHoaID"] is Guid) return (Guid)_DataRow["VatTuHangHoaID"];
                return Guid.Empty;
            }
            set
            {
                _DataRow["VatTuHangHoaID"] = value;
            }
        }
        #endregion

        #region string SoChungTu property
        public string SoChungTu
        {
            get
            {
                if (_DataRow["SoChungTu"] is string) return (string)_DataRow["SoChungTu"];
                return "";
            }
            set
            {
                _DataRow["SoChungTu"] = value;
            }
        }
        #endregion

        #region DateTime? property
        public DateTime? NgayChungTu
        {
            get
            {
                if (DataRow["NgayChungTu"] is DateTime) return (DateTime)DataRow["NgayChungTu"];
                else return null;
            }
            set
            {
                if (value is DateTime)
                    DataRow["NgayChungTu"] = (DateTime)value;
                else
                    DataRow["NgayChungTu"] = System.DBNull.Value;
            }
        }
        #endregion

        #region DateTime? property
        public DateTime? DateExpire
        {
            get
            {
                if (DataRow["DateExpire"] is DateTime) return (DateTime)DataRow["DateExpire"];
                else return null;
            }
            set
            {
                if (value is DateTime)
                    DataRow["DateExpire"] = (DateTime)value;
                else
                    DataRow["DateExpire"] = System.DBNull.Value;
            }
        }
        #endregion

        #region decimal SoLuongTon property
        public decimal SoLuongTon
        {
            get
            {
                if (_DataRow["SoLuongTon"] is decimal) return (decimal)_DataRow["SoLuongTon"];
                return 0;
            }
            set
            {
                _DataRow["SoLuongTon"] = value;
            }
        }
        #endregion

        #region string SoLuongTonTheoKhoDangText property
        public string SoLuongTonTheoKhoDangText
        {
            get
            {
                if (_DataRow["SoLuongTonTheoKhoDangText"] is string) return (string)_DataRow["SoLuongTonTheoKhoDangText"];
                return "";
            }
            set
            {
                _DataRow["SoLuongTonTheoKhoDangText"] = value;
            }
        }
        #endregion

        #region decimal SoLuongNhap property
        public decimal SoLuongNhap
        {
            get
            {
                if (_DataRow["SoLuongNhap"] is decimal) return (decimal)_DataRow["SoLuongNhap"];
                return 0;
            }
            set
            {
                _DataRow["SoLuongNhap"] = value;
            }
        }
        #endregion

        #region string SoLo property
        public string SoLo
        {
            get
            {
                if (_DataRow["SoLo"] is string) return (string)_DataRow["SoLo"];
                return "";
            }
            set
            {
                _DataRow["SoLo"] = value;
            }
        }
        #endregion

        #region int NgayConLai property
        public int NgayConLai
        {
            get
            {
                if (_DataRow["NgayConLai"] is int) return (int)_DataRow["NgayConLai"];
                return 0;
            }
            set
            {
                _DataRow["NgayConLai"] = value;
            }
        }
        #endregion

        public HanSuDung(DataRow dataRow)
            : base(dataRow)
        {
        }
    }

    public class DanhMucHanSuDung : DanhMucDoiTuong, IEnumerable
    {
        #region Instance property: Singleton Design pattern
        private static DanhMucHanSuDung _Instance;
        public static DanhMucHanSuDung Instance
        {
            get
            {
                if (_Instance == null)
                {
                    _Instance = new DanhMucHanSuDung();
                    _Instance.DataView = _Instance.DataTable.DefaultView;
                }
                return _Instance;
            }
        }
        #endregion

        #region override TableName property
        public override string TableName
        {
            get
            {
                return "HanSuDung";
            }
        }
        #endregion

        #region Methods required by IEnumerable (use for foreach)
        public IEnumerator GetEnumerator()
        {
            return new TokenEnumerator(this);
        }

        private class TokenEnumerator : IEnumerator
        {
            private int _Position = -1;
            DanhMucDoiTuong _Dstk;

            public TokenEnumerator(DanhMucDoiTuong dstk)
            {
                _Dstk = dstk;
            }

            // Declare the MoveNext method required by IEnumerator:
            public bool MoveNext()
            {
                if (_Position < _Dstk.DataView.Count - 1)
                {
                    _Position++;
                    return true;
                }
                else
                {
                    return false;
                }
            }

            // Declare the Reset method required by IEnumerator:
            public void Reset()
            {
                _Position = -1;
            }

            // Declare the Current property required by IEnumerator:
            public object Current
            {
                get
                {
                    return new HanSuDung((_Dstk.DataView[_Position].Row));
                }
            }
        }
        #endregion

        public HanSuDung this[int index]
        {
            get
            {
                if (0 <= index && index <= this.Count)
                {
                    return new HanSuDung(DataTable.Rows[index]);
                }
                else
                {
                    return null;
                }
            }
        }

        public HanSuDung NewDoiTuong()
        {
            return new HanSuDung(DataTable.NewRow());
        }

        public HanSuDung GetByProperty(string property, object value)
        {
            DataRow[] rows = GetDataRows(property, value);
            if (rows.Length > 0) return new HanSuDung(rows[0]);
            else return null;
        }

        public HanSuDung GetByID(Guid objID)
        {
            return GetByProperty("ID", objID);
        }

        public HanSuDung GetByVatTuHangHoaID(Guid VatTuHangHoaID)
        {
            return GetByProperty("VatTuHangHoaID", VatTuHangHoaID);
        }

        public static DanhMucHanSuDung GetAll()
        {
            return Instance;
        }

        public static DanhMucHanSuDung Filter(string filterExpression)
        {
            try
            {
                DanhMucHanSuDung obj = new DanhMucHanSuDung();
                obj.DataView = new DataView(DanhMucHanSuDung.Instance.DataTable, filterExpression, "", DataViewRowState.CurrentRows);
                return obj;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public  DataTable LoadLichSuMuaHangTheoNgayChungTuForCheckHanSuDungFromDB(Guid vthhID)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return LoadLichSuMuaHangTheoNgayChungTuForCheckHanSuDungFromDB(cmd, vthhID);
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        public  DataTable LoadLichSuMuaHangTheoNgayChungTuForCheckHanSuDungFromDB(SqlCommand cmd, Guid vthhID)
        {
            try
            {
                DateTime thoiDiemTinh = DateTime.Now;
                ThoiDiemKhoaSo thoiDiemKhoaSoGanNhat = DanhMucThoiDiemKhoaSo.Instance.GetThoiDiemChotSoDuGanNhat(DateTime.Now);
                if (thoiDiemKhoaSoGanNhat == null) throw new CustomException("Không có thời điểm khóa sổ trước " + thoiDiemTinh.ToString());

                cmd.CommandText = string.Format("SELECT  CASE \n" +
                                              "              WHEN A.SoChungTuGoc = '' THEN A.So \n" +
                                              "              WHEN A.SoChungTuGoc <> '' THEN B.So \n" +
                                              "        END SoChungTu, C.Ngay NgayChungTu, C.SoLo, SoLuong SoLuongNhap, DateExpire, ABS(DATEDIFF(DD, DateExpire, GETDATE())) NgayConLai \n" +
                                              "FROM ChungTu A LEFT JOIN ChungTu B ON A.ChungTuGocID = B.ChungTuID \n" +
                                              "	INNER JOIN NhatKyNhapKho C ON C.ChungTuID = A.ChungTuID --NhatKyNhapKho chứa số phiếu \n" +
                                              "WHERE VatTuHangHoaID = '{0}' \n" +
                                              "ORDER BY C.Ngay DESC "
                                            , vthhID);

                cmd.Parameters.Clear();
                cmd.Parameters.Add(new SqlParameter("ThoiDiemDau", SqlDbType.DateTime));
                cmd.Parameters["ThoiDiemDau"].Value = thoiDiemKhoaSoGanNhat.ThoiDiem;
                cmd.Parameters.Add(new SqlParameter("ThoiDiemCuoi", SqlDbType.DateTime));
                cmd.Parameters["ThoiDiemCuoi"].Value = thoiDiemTinh;

                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public  DataTable LoadThuTuNhapHangTheoHanSuDungForCheckHanSuDungFromDB(Guid vthhID)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return LoadThuTuNhapHangTheoHanSuDungForCheckHanSuDungFromDB(cmd, vthhID);
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        public  DataTable LoadThuTuNhapHangTheoHanSuDungForCheckHanSuDungFromDB(SqlCommand cmd, Guid vthhID)
        {
            try
            {
                DateTime thoiDiemTinh = DateTime.Now;
                ThoiDiemKhoaSo thoiDiemKhoaSoGanNhat = DanhMucThoiDiemKhoaSo.Instance.GetThoiDiemChotSoDuGanNhat(DateTime.Now);
                if (thoiDiemKhoaSoGanNhat == null) throw new CustomException("Không có thời điểm khóa sổ trước " + thoiDiemTinh.ToString());

                cmd.CommandText = string.Format("SELECT  CASE \n" +
                                                "              WHEN A.SoChungTuGoc = '' THEN A.So \n" +
                                                "              WHEN A.SoChungTuGoc <> '' THEN B.So \n" +
                                                "        END SoChungTu, C.Ngay NgayChungTu, C.SoLo, SoLuong SoLuongNhap, DateExpire, ABS(DATEDIFF(DD, DateExpire, GETDATE())) NgayConLai \n" +
                                                "FROM ChungTu A LEFT JOIN ChungTu B ON A.ChungTuGocID = B.ChungTuID \n" +
                                                "	INNER JOIN NhatKyNhapKho C ON C.ChungTuID = A.ChungTuID --NhatKyNhapKho chứa số phiếu \n" +
                                                "WHERE VatTuHangHoaID = '{0}' AND @ThoiDiemDau <= C.Ngay AND C.Ngay <= @ThoiDiemCuoi \n" +
                                                "ORDER BY C.DateExpire DESC "
                                              , vthhID);

                cmd.Parameters.Clear();
                cmd.Parameters.Add(new SqlParameter("ThoiDiemDau", SqlDbType.DateTime));
                cmd.Parameters["ThoiDiemDau"].Value = thoiDiemKhoaSoGanNhat.ThoiDiem;
                cmd.Parameters.Add(new SqlParameter("ThoiDiemCuoi", SqlDbType.DateTime));
                cmd.Parameters["ThoiDiemCuoi"].Value = thoiDiemTinh;

                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public  HanSuDung GetHanSuDungByHangHoa(Guid vthhID)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                return GetHanSuDungByHangHoa(cmd, vthhID);
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        public  HanSuDung GetHanSuDungByHangHoa(SqlCommand cmd, Guid vthhID)
        {
            if (vthhID == Guid.Empty) return null;
            HanSuDung HSD = NewDoiTuong();
            try
            {
                TonKho tonToanBoCacKho = TonKho.CalculateTongTonTuThoiDiemKhoaSoGanNhatDenThoiDiemNhapVao(cmd, DateTime.Now, vthhID);
                DataTable dt = LoadThuTuNhapHangTheoHanSuDungForCheckHanSuDungFromDB(cmd, vthhID);
                decimal temp = tonToanBoCacKho.SoLuong;
                foreach (DataRow rowForDate in dt.Rows)
                {
                    if (rowForDate["SoLuongNhap"] is decimal && rowForDate["DateExpire"] is DateTime && temp - (decimal)rowForDate["SoLuongNhap"] < 0)
                    {
                        HSD.VatTuHangHoaID = vthhID;
                        if (rowForDate["NgayChungTu"] is DateTime) HSD.NgayChungTu = (DateTime)rowForDate["NgayChungTu"];
                        else HSD.NgayChungTu = null;
                        if (rowForDate["DateExpire"] is DateTime) HSD.DateExpire = (DateTime)rowForDate["DateExpire"];
                        else HSD.DateExpire = null;
                        HSD.SoLuongTon = tonToanBoCacKho.SoLuong;
                        HSD.SoLuongNhap = (decimal)rowForDate["SoLuongNhap"];
                        HSD.NgayConLai = (int)rowForDate["NgayConLai"];
                        HSD.SoChungTu = rowForDate["SoChungTu"].ToString();
                        HSD.SoLo = rowForDate["SoLo"].ToString();

                        return HSD;
                    }
                    temp -= (decimal)rowForDate["SoLuongNhap"];
                }
                return null;
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public void InsertOrUpdateByHangHoaDungTrongNhapXuatHang(Guid vthhID, Guid khoIDUpdate, decimal soLuongUpdate, HanSuDung.UpdateSoLuongTrongHanSuDung kieuTinhToan)
        {
            SqlConnection con = new SqlConnection(GeneralLibs.LocalSettings.ConnectString);
            try
            {
                con.Open();
                SqlCommand cmd = con.CreateCommand();
                InsertOrUpdateByHangHoaDungTrongNhapXuatHang(cmd, vthhID, khoIDUpdate, soLuongUpdate, kieuTinhToan);
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }
        public void InsertOrUpdateByHangHoaDungTrongNhapXuatHang(SqlCommand cmd, Guid vthhID, Guid khoIDUpdate, decimal soLuongUpdate, HanSuDung.UpdateSoLuongTrongHanSuDung kieuTinhToan)
        {
            if (vthhID == Guid.Empty) return;
            try
            {
                //Tồn này là tính trong event RowChanged của các phiếu --> cần cộng trừ thêm số lượng tùy từng phiếu
                string tonTungKhoTheoFormat = "";
                foreach (Kho kho in DanhMucKho.Instance)
                {
                    TonKho tonKhoTungKho = TonKho.CalculateTonTuThoiDiemKhoaSoGanNhatDenThoiDiemNhapVaoTheoKho(cmd, DateTime.Now, kho.ID, vthhID);
                    if (khoIDUpdate == kho.ID)
                    {
                        if (kieuTinhToan == HanSuDung.UpdateSoLuongTrongHanSuDung.Tang)
                            tonKhoTungKho.SoLuong += soLuongUpdate; //Vì phiếu này chưa Lưu nên tự cộng thêm
                        else if (kieuTinhToan == HanSuDung.UpdateSoLuongTrongHanSuDung.Giam)
                            tonKhoTungKho.SoLuong -= soLuongUpdate; //Vì phiếu này chưa Lưu nên tự trừ đi
                    }
                    tonTungKhoTheoFormat += string.Format("{0}%{1}%", kho.Ma, Convert.ToInt32(tonKhoTungKho.SoLuong));
                }
                TonKho tonToanBoCacKho = TonKho.CalculateTongTonTuThoiDiemKhoaSoGanNhatDenThoiDiemNhapVao(cmd, DateTime.Now, vthhID);
                if (kieuTinhToan == HanSuDung.UpdateSoLuongTrongHanSuDung.Tang)
                    tonToanBoCacKho.SoLuong += soLuongUpdate; //Vì phiếu này chưa Lưu nên tự cộng thêm
                else if (kieuTinhToan == HanSuDung.UpdateSoLuongTrongHanSuDung.Giam)
                    tonToanBoCacKho.SoLuong -= soLuongUpdate; //Vì phiếu này chưa Lưu nên tự trừ đi

                HanSuDung HSD = DanhMucHanSuDung.Instance.GetByVatTuHangHoaID(vthhID);

                //Nếu xuất âm thì k cần tính toán hạn sử dụng nữa
                if(HSD != null && tonToanBoCacKho != null && tonToanBoCacKho.SoLuong < 0)
                {
                    HSD.DateExpire = null;
                    HSD.NgayChungTu = null;
                    HSD.SoLuongTon = tonToanBoCacKho.SoLuong;
                    HSD.SoLuongTonTheoKhoDangText = tonTungKhoTheoFormat;
                    HSD.SoLuongNhap = 0;
                    HSD.NgayConLai = 0;
                    HSD.SoChungTu = "";
                    HSD.SoLo = "";

                    HSD.UpdateToDB();
                    return;
                }

                DataTable dt = LoadThuTuNhapHangTheoHanSuDungForCheckHanSuDungFromDB(cmd, vthhID);
                decimal temp = tonToanBoCacKho.SoLuong;

                if (HSD != null)
                {
                    foreach (DataRow rowForDate in dt.Rows)
                    {
                        if (rowForDate["SoLuongNhap"] is decimal && rowForDate["DateExpire"] is DateTime && temp - (decimal)rowForDate["SoLuongNhap"] < 0)
                        {
                            if (rowForDate["NgayChungTu"] is DateTime) HSD.NgayChungTu = (DateTime)rowForDate["NgayChungTu"];
                            else HSD.NgayChungTu = null;
                            if (rowForDate["DateExpire"] is DateTime) HSD.DateExpire = (DateTime)rowForDate["DateExpire"];
                            else HSD.DateExpire = null;
                            //HSD.SoLuongTon = tonToanBoCacKho.SoLuong;
                            HSD.SoLuongTon = temp;  //Chỉ hiện số lượng tồn của lô đó
                            HSD.SoLuongTonTheoKhoDangText = tonTungKhoTheoFormat;
                            HSD.SoLuongNhap = (decimal)rowForDate["SoLuongNhap"];
                            HSD.NgayConLai = (int)rowForDate["NgayConLai"];
                            HSD.SoChungTu = rowForDate["SoChungTu"].ToString();
                            HSD.SoLo = rowForDate["SoLo"].ToString();

                            HSD.UpdateToDB();
                            return;
                        }
                        temp -= (decimal)rowForDate["SoLuongNhap"];
                    }
                    //Ra được ngoài này là hàng chưa dùng hoặc mới dùng hết tồn đầu kỳ. Set lại tồn và cho hạn sử dụng = 0
                    HSD.DateExpire = null;
                    HSD.NgayChungTu = null;
                    HSD.SoLuongTon = tonToanBoCacKho.SoLuong;
                    HSD.SoLuongTonTheoKhoDangText = tonTungKhoTheoFormat;
                    HSD.SoLuongNhap = 0;
                    HSD.NgayConLai = 0;
                    HSD.SoChungTu = "";
                    HSD.SoLo = "";

                    HSD.UpdateToDB();
                }
                else
                {
                    HSD = NewDoiTuong();

                    //Chưa biết hạn thế nào. ĐÚng ra thuốc nào đã mua bán cũng phải có trong bảng này.
                    //Vậy insert luôn tồn kho, hạn tính sau
                    HSD.VatTuHangHoaID = vthhID;
                    HSD.SoLuongTon = tonToanBoCacKho.SoLuong;
                    HSD.SoLuongTonTheoKhoDangText = tonTungKhoTheoFormat;
                    HSD.InsertToDB();

                    foreach (DataRow rowForDate in dt.Rows)
                    {
                        if (rowForDate["SoLuongNhap"] is decimal && rowForDate["DateExpire"] is DateTime && temp - (decimal)rowForDate["SoLuongNhap"] < 0)
                        {
                            if (rowForDate["NgayChungTu"] is DateTime) HSD.NgayChungTu = (DateTime)rowForDate["NgayChungTu"];
                            else HSD.NgayChungTu = null;
                            if (rowForDate["DateExpire"] is DateTime) HSD.DateExpire = (DateTime)rowForDate["DateExpire"];
                            else HSD.DateExpire = null;
                            HSD.SoLuongNhap = (decimal)rowForDate["SoLuongNhap"];
                            HSD.NgayConLai = (int)rowForDate["NgayConLai"];
                            HSD.SoChungTu = rowForDate["SoChungTu"].ToString();
                            HSD.SoLo = rowForDate["SoLo"].ToString();

                            HSD.UpdateToDB();
                            return;
                        }
                        temp -= (decimal)rowForDate["SoLuongNhap"];
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                throw new CustomException(CustomException.GetSqlExceptionMessage(sqlEx));
            }
            catch (Exception ex)
            {
                if (ex is CustomException) throw ex;
                else
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        public void UpdateHashableToDB(Hashtable htHanSuDung)
        {
            //Xóa các row trong dtNhoGia khỏi CSDL
            foreach (DictionaryEntry entry in htHanSuDung)
            {
                InsertOrUpdateByHangHoaDungTrongNhapXuatHang((Guid)entry.Key, Guid.Empty, (decimal)0, HanSuDung.UpdateSoLuongTrongHanSuDung.KhongTangKhongGiam);
            }
        }
        public void UpdateArrayListOfVatTuHangHoaToDB(ArrayList htarrayVTHH)
        {
            //Xóa các row trong dtNhoGia khỏi CSDL
            foreach (object vthhID in htarrayVTHH)
            {
                InsertOrUpdateByHangHoaDungTrongNhapXuatHang((Guid)vthhID, Guid.Empty, 0, HanSuDung.UpdateSoLuongTrongHanSuDung.KhongTangKhongGiam);
            }
        }
        
    }
}
